package leagal_aid.bean;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import leagal_aid.bean.LeagalBean;
import leagal_aid.bean.Admin;
import leagal_aid.util.DBUtil;
             //类名
public class LeagalBean {
	//属性（成员变量）
	private int id;
	private String name;
	private String category;
	private String username;
	private String password;
	private String phonenumber;
	private String title;
	private String content;
	private int homepage;
	private String author;
	private Date time;
	private String picturetittle;
	private int picturepostion;
	private String source;
	private String link;
	private int puton;
	private String picture;
	

	PreparedStatement statement=null;
	ResultSet rs = null;
	Connection connection = null;
	
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getName() {
		return name;
	}
	public void getName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getPhonenumber() {
		return phonenumber;
	}
	public void setPhonenumber(String phonenumber) {
		this.phonenumber = phonenumber;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	public String getCategory() {
		return category;
	}
	public void setCategory(String category) {
		this.category = category;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String tittle) {
		this.title = tittle;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public int getHomepage() {
		return homepage;
	}
	public void setHomepage(int homepage) {
		this.homepage = homepage;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public Date getTime() {
		return time;
	}
	public void setTime(Date time) {
		this.time = time;
	}
	public String getPicturetittle() {
		return picturetittle;
	}
	public void setPicturetittle(String picturetittle) {
		this.picturetittle = picturetittle;
	}
	public int getPicturepostion() {
		return picturepostion;
	}
	public void setPicturepostion(int picturepostion) {
		this.picturepostion = picturepostion;
	}
	public String getSource() {
		return source;
	}
	public void setSource(String source) {
		this.source = source;
	}
	public String getLink() {
		return link;
	}
	public void setLink(String link) {
		this.link = link;
	}
	public String getPicture () {
		return picture; 
	}
	public void setPicture(String picture) {
		this.picture = picture;
	}
	public int getPuton() {
		return puton;
	}
	public void setPuton(int puton) {
		this.puton = puton;
	}
	//查询全部内容
	public List<LeagalBean> findAll() {
		List<LeagalBean> list = new ArrayList<LeagalBean>();
		DBUtil dbUtil = new DBUtil();
		String sql = "select * from team";
		connection = dbUtil.geConnection();
		try {
			statement = connection.prepareStatement(sql);
			rs = statement.executeQuery();
			while(rs.next()) {
				LeagalBean leagalBean = new LeagalBean();
				leagalBean.setId(rs.getInt("id"));
				leagalBean.setTitle(rs.getString("title"));
				leagalBean.setContent(rs.getString("content"));
				//leagalBean.setAuthor(rs.getString("author"));
				//leagalBean.setSource(rs.getString("source"));
				//leagalBean.setTime(rs.getDate("time"));
				//leagalBean.setPicture(rs.getString("picture"));
			
				list.add(leagalBean);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return list;
		
	}
	
	
	//模糊查询
	 public List<LeagalBean> Proselect(String lbString,String tilString,Page page) {
		    List<LeagalBean> list =new ArrayList<LeagalBean>();
		    DBUtil dbUtil = new DBUtil();
		    connection = dbUtil.geConnection();
		    //String sql = "select * from news";
		    String sql ="select * from news where category =? and concat(id,title,content,category,author,source,time,picture,homepage,link) like ? order by time desc limit ?,?" ;
		    connection = dbUtil.geConnection();
		    try {
		     statement = connection.prepareStatement(sql);
		     statement.setString(1, lbString);
		     statement.setString(2,"%" + tilString + "%");
		     statement.setInt(3, page.getBeginIndex());
		     statement.setInt(4, page.getEveryPage());
		     rs = statement.executeQuery();
		     while(rs.next()) {
		    	   LeagalBean leagalBean = new LeagalBean();
				   leagalBean.setId(rs.getInt("id"));
				   leagalBean.setTitle(rs.getString("title"));
				   leagalBean.setContent(rs.getString("content"));
				   leagalBean.setCategory(rs.getString("category"));
				   leagalBean.setAuthor(rs.getString("author"));
				   leagalBean.setSource(rs.getString("source"));
				   leagalBean.setTime(rs.getDate("time"));
				   leagalBean.setPicture(rs.getString("picture"));
				   leagalBean.setHomepage(rs.getInt("homepage"));
				   leagalBean.setLink(rs.getString("link"));
				   list.add(leagalBean);
		     }
		     
		    } catch (SQLException e) {
		     e.printStackTrace();
		    }finally {
		     if (rs != null) {
		      try {
		       rs.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if (statement != null) {
		      try {
		       statement.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if(connection != null) {
		      try {
		       connection.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     dbUtil.close();
		    }
		    return list;
		    
		   }

	 
	 public List<LeagalBean> adminProselect(String tilString,Page page) {
		    List<LeagalBean> list =new ArrayList<LeagalBean>();
		    DBUtil dbUtil = new DBUtil();
		    connection = dbUtil.geConnection();
		    //String sql = "select * from news";
		    String sql ="select * from news where category =? and concat(id,title,content,category,author,source,time,picture,homepage,link) like ? order by time desc limit ?,?" ;
		    connection = dbUtil.geConnection();
		    try {
		     statement = connection.prepareStatement(sql);
		     statement.setString(1,"%" + tilString + "%");
		     statement.setInt(2, page.getBeginIndex());
		     statement.setInt(3, page.getEveryPage());
		     rs = statement.executeQuery();
		     while(rs.next()) {
		    	   LeagalBean leagalBean = new LeagalBean();
				   leagalBean.setId(rs.getInt("id"));
				   leagalBean.setTitle(rs.getString("title"));
				   leagalBean.setContent(rs.getString("content"));
				   leagalBean.setCategory(rs.getString("category"));
				   leagalBean.setAuthor(rs.getString("author"));
				   leagalBean.setSource(rs.getString("source"));
				   leagalBean.setTime(rs.getDate("time"));
				   leagalBean.setPicture(rs.getString("picture"));
				   leagalBean.setHomepage(rs.getInt("homepage"));
				   leagalBean.setLink(rs.getString("link"));
				   list.add(leagalBean);
		     }
		     
		    } catch (SQLException e) {
		     e.printStackTrace();
		    }finally {
		     if (rs != null) {
		      try {
		       rs.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if (statement != null) {
		      try {
		       statement.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if(connection != null) {
		      try {
		       connection.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     dbUtil.close();
		    }
		    return list;
		    
		   }

	 public List<LeagalBean> successProselect(String lbString,String tilString,Page page) {
		    List<LeagalBean> list =new ArrayList<LeagalBean>();
		    DBUtil dbUtil = new DBUtil();
		    connection = dbUtil.geConnection();
		    //String sql = "select * from news";
		    String sql ="select * from news where category =success and concat(id,title,content,category,author,source,time,picture,homepage,link) like ? order by time desc limit ?,?" ;
		    connection = dbUtil.geConnection();
		    try {
		     statement = connection.prepareStatement(sql);
		     statement.setString(1, lbString);
		     statement.setString(2,"%" + tilString + "%");
		     statement.setInt(3, page.getBeginIndex());
		     statement.setInt(4, page.getEveryPage());
		     rs = statement.executeQuery();
		     while(rs.next()) {
		    	   LeagalBean leagalBean = new LeagalBean();
				   leagalBean.setId(rs.getInt("id"));
				   leagalBean.setTitle(rs.getString("title"));
				   leagalBean.setContent(rs.getString("content"));
				   leagalBean.setCategory(rs.getString("category"));
				   leagalBean.setAuthor(rs.getString("author"));
				   leagalBean.setSource(rs.getString("source"));
				   leagalBean.setTime(rs.getDate("time"));
				   leagalBean.setPicture(rs.getString("picture"));
				   leagalBean.setHomepage(rs.getInt("homepage"));
				   leagalBean.setLink(rs.getString("link"));
				   list.add(leagalBean);
		     }
		     
		    } catch (SQLException e) {
		     e.printStackTrace();
		    }finally {
		     if (rs != null) {
		      try {
		       rs.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if (statement != null) {
		      try {
		       statement.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     if(connection != null) {
		      try {
		       connection.close();
		      } catch (SQLException e) {
		       e.printStackTrace();
		      }
		     }
		     dbUtil.close();
		    }
		    return list;
		    
		   }
	
	
	 
   	
		public boolean doAdd(String category,String title,String content,String picture,String time,String link,String source,int homepage,String author) {
			boolean add = false;
			DBUtil dbUtil = new DBUtil();
			connection = dbUtil.geConnection();
			//String sql = "select password from admin where username = " + username + "'";
			String sql = "insert into news(category,title,content,picture,time,link,source,homepage,author) values(?,?,?,?,?,?,?,?,?)";
			
		  try {		
			  PreparedStatement statement = connection.prepareStatement(sql);
				statement.setString(1, category);
				statement.setString(2, title);
				statement.setString(3, content);
				statement.setString(4, picture);
				statement.setString(5, time);
				statement.setString(6, link);
				statement.setString(7, source);
				statement.setInt(8, homepage);
				statement.setString(9, author);
			
				int i = statement.executeUpdate();
				if (i>0) {
					add = true;
				}
				System.out.println(add);
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if (statement != null) {
					try {
						statement.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				if(connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				dbUtil.close();
			}
			return add;
			
		}
		
		
	
	public boolean newsAdd(String title,String content,String picture,String time) {
		boolean add = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "insert into team(title,content,picture,time) values(?,?,?,?)";
		
	  try {		
		  PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, title);
			statement.setString(2, content);
			statement.setString(3, picture);
			statement.setString(4, time);
		
			int i = statement.executeUpdate();
			if (i>0) {
				add = true;
			}
			System.out.println(add);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return add;
		
	}
	
	
	
	public boolean doDel(int id) {
		boolean del = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "delete from team where id =?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			int i = statement.executeUpdate();
			if (i>0) {
				del = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return del;
		
	}
	
	
	public boolean serviceDel(int id) {
		boolean del = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "delete from news where id =?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			int i = statement.executeUpdate();
			if (i>0) {
				del = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return del;
		
	}
	public boolean newsDel(int id) {
		boolean del = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "delete from news where id =?";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			int i = statement.executeUpdate();
			if (i>0) {
				del = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return del;
		
	}
	
	
	public boolean doUpdata(String category,String title,String content,Integer homepage,String picture,String author,String source,String txtDate,int id) {
		boolean updata = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "update news set category =?,title =?,content =?,homepage =?,picture =?,author =?,source=?,time=? where id =?";
		try {
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, category);
			statement.setString(2, title);
			statement.setString(3, content);
			statement.setInt(4, homepage);
			statement.setString(5, picture);
			statement.setString(6, author);
			statement.setString(7, source);
			statement.setString(8, txtDate);
			//statement.setDate(8, (java.sql.Date) date1);
			statement.setInt(9, id);
			System.out.println(updata);
			int i = statement.executeUpdate();
			if (i>0) {
				updata = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return updata;
		
	}
	public boolean DoUpdata(String category,String content,Integer homepage,String source,String author,String picture,String txtDate,String title,int id) {
		boolean updata = false;
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		//String sql = "select password from admin where username = " + username + "'";
		String sql = "update news set category =?,content =?,homepage =?,source=?,author =?,picture =?,time=?,title =? where id =?";
		try {
			PreparedStatement statement = connection.prepareStatement(sql);
			statement.setString(1, category);
			statement.setString(2, content);
			statement.setInt(3, homepage);
			statement.setString(4, source);
			statement.setString(5, author);
			statement.setString(6,picture );
			statement.setString(7, txtDate);
			statement.setString(8, title);
			//statement.setDate(8, (java.sql.Date) date1);
			statement.setInt(9, id);
			System.out.println(updata);
			int i = statement.executeUpdate();
			if (i>0) {
				updata = true;
			}
				
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		return updata;
		
	}
	

	//通过id查找内容
public LeagalBean findNewsById(int id) {
		System.out.println("run findNewsById : " + id);
		DBUtil dbUtil = new DBUtil();
		connection = dbUtil.geConnection();
		LeagalBean leagalBean = new LeagalBean();
		String sql = "select * from news where id = ? order by time desc ";
		try {
			statement = connection.prepareStatement(sql);
			statement.setInt(1, id);
			rs = statement.executeQuery();
			if(rs.next()) {
				 leagalBean.setId(rs.getInt("id"));
				 leagalBean.setTitle(rs.getString("title"));
				 leagalBean.setContent(rs.getString("content"));
				 leagalBean.setTime(rs.getDate("time"));
				 leagalBean.setPicture(rs.getString("picture"));
				 leagalBean.setAuthor(rs.getString("author"));
				 leagalBean.setLink(rs.getString("link"));
				 leagalBean.setSource(rs.getString("source"));
				 leagalBean.setCategory(rs.getString("category"));
				 leagalBean.setHomepage(rs.getInt("homepage"));
				System.out.println(leagalBean.getPicture());
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			dbUtil.close();
		}
		System.out.println( "end findNewsById :");
		return leagalBean;
		
	}


public LeagalBean findPreNewsById(String lb,int id) {
	System.out.println("run findNewsByid  nad ib");
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	LeagalBean leagalBean = new LeagalBean();
	String sql = "select * from news where id = (select id from news where category =? and id < ? order by id desc limit 1)";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lb);
		statement.setInt(2, id);
		System.out.println(sql);
		rs = statement.executeQuery();
		System.out.println(sql);
		
		if(rs.next()) {
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			leagalBean.setTime(rs.getDate("time"));
			leagalBean.setAuthor(rs.getString("author"));
			leagalBean.setSource(rs.getString("source"));
			leagalBean.setPicture(rs.getString("picture"));
			
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}

	return leagalBean;
	
}



public LeagalBean findNextNewsById( String lb,int id) {
	
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	LeagalBean leagalBean = new LeagalBean();
	String sql = "select * from news where id = (select id from news where category =? and id > ?  order by id asc limit 1)";
	try {
		
		statement = connection.prepareStatement(sql);
		statement.setString(1, lb);
		statement.setInt(2, id);
		System.out.println(sql);
		rs = statement.executeQuery();
		System.out.println(sql);
		if(rs.next()) {
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			leagalBean.setTime(rs.getDate("time"));
			leagalBean.setAuthor(rs.getString("author"));
			leagalBean.setSource(rs.getString("source"));
			leagalBean.setPicture(rs.getString("picture"));
			System.out.println(leagalBean.getPicture());
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}

	return leagalBean;
	
}

public List<LeagalBean> findAllByLb(String lbString) {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =?";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			System.out.println(leagalBean.getPicture());
			list.add(leagalBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

public int findCountByLb(String lbString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from news where category =?";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}


	
public List<LeagalBean> findAllByLbForFY(String lbString,Page page) {
	  List<LeagalBean> list = new ArrayList<LeagalBean>();
	  DBUtil dbUtil = new DBUtil(); 
	  connection = dbUtil.geConnection(); 
	  String sql =  "select * from news where category =? order by time desc limit ?,?"; 
	  try {
	  statement = connection.prepareStatement(sql); 
	  statement.setString(1, lbString);
	  statement.setInt(2, page.getBeginIndex());
	  statement.setInt(3, page.getEveryPage()); 
	  rs = statement.executeQuery(); 
	  while(rs.next()) {
	  LeagalBean leagalBean = new LeagalBean();
	  leagalBean.setCategory(rs.getString("category"));
	  leagalBean.setId(rs.getInt("id")); leagalBean.setTitle(rs.getString("title"));
	  leagalBean.setContent(rs.getString("content"));
	  leagalBean.setAuthor(rs.getString("author"));
	  leagalBean.setSource(rs.getString("source"));
	  leagalBean.setTime(rs.getDate("time"));
	  leagalBean.setPicture(rs.getString("picture"));
	  System.out.println(leagalBean.getPicture()); 
	  list.add(leagalBean); 
	  }
} catch (SQLException e) { 
	e.printStackTrace();
	}finally {
		if (rs != null) {
	  try { 
		  rs.close(); 
	  } catch (SQLException e) { 
		  e.printStackTrace(); 
		  } 
	  } if
	  (statement != null) { 
		  try { 
			  statement.close(); 
			  } catch (SQLException e) {
	  e.printStackTrace(); 
	  } 
		  } if(connection != null) { 
			  try { connection.close(); 
			  }
	  catch (SQLException e) { 
		  e.printStackTrace(); 
		  } 
			  } dbUtil.close(); 
			  } return  list;
	  
	  }
	 
	


public List<LeagalBean> teamdelAll() {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	DBUtil dbUtil = new DBUtil();
	String sql = "select * from team";
	connection = dbUtil.geConnection();
	try {
		statement = connection.prepareStatement(sql);
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			//leagalBean.setAuthor(rs.getString("author"));
			//leagalBean.setSource(rs.getString("source"));
			//leagalBean.setTime(rs.getDate("time"));
			//leagalBean.setPicture(rs.getString("picture"));
		
			list.add(leagalBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}	


public List<LeagalBean> teamAll() {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	//String sql = "select password from admin where username = " + username + "'";
	String sql = "select * from team";
	
	try {
		statement = connection.prepareStatement(sql);
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setId(rs.getInt("id"));
			leagalBean.getName(rs.getString("name"));
			leagalBean.setTime(rs.getDate("time"));
			list.add(leagalBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

public List<LeagalBean> newsfindAllByLb(String lbString) {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =?";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			    leagalBean.setContent(rs.getString("content"));
			    leagalBean.setPicture(rs.getString("picture"));
			    leagalBean.setTime(rs.getDate("time"));
			    leagalBean.setAuthor(rs.getString("author"));
			    leagalBean.setSource(rs.getString("source"));
			    leagalBean.setLink(rs.getString("link"));
			    leagalBean.setPuton(rs.getInt("puton"));
			    leagalBean.setCategory(rs.getString("category"));
			System.out.println(leagalBean.getPicture());
			list.add(leagalBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}



public int findbackCountByLb(String lbString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from news where category =? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}

public int findbackCountByAll() {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from admin ";
	try {
		statement = connection.prepareStatement(sql);
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}
//后端分页
public List<LeagalBean> findbackByLbForFY(String lbString,Page page) {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =? order by time desc limit ?,? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		statement.setInt(2, page.getBeginIndex());
		statement.setInt(3, page.getEveryPage());
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setCategory(rs.getString("category"));
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			leagalBean.setAuthor(rs.getString("author"));
			leagalBean.setSource(rs.getString("source"));
			leagalBean.setTime(rs.getDate("time"));
			leagalBean.setPicture(rs.getString("picture"));
			leagalBean.setLink(rs.getString("link"));
			leagalBean.setHomepage(rs.getInt("homepage"));
			System.out.println(leagalBean.getPicture());
			list.add(leagalBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

public List<LeagalBean> findbackByAllForFY(Page page) {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =? order by time desc limit ?,? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setInt(1, page.getBeginIndex());
		statement.setInt(2, page.getEveryPage());
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setCategory(rs.getString("category"));
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			leagalBean.setAuthor(rs.getString("author"));
			leagalBean.setSource(rs.getString("source"));
			leagalBean.setTime(rs.getDate("time"));
			leagalBean.setPicture(rs.getString("picture"));
			System.out.println(leagalBean.getPicture());
			list.add(leagalBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}


public List<LeagalBean> findALLForFY(String lbString,Page page) {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	String sql = "select * from news where category =? order by time desc limit ?,? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		statement.setInt(2, page.getBeginIndex());
		statement.setInt(3, page.getEveryPage());
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			leagalBean.setAuthor(rs.getString("author"));
			leagalBean.setSource(rs.getString("source"));
			leagalBean.setTime(rs.getDate("time"));
			leagalBean.setPicture(rs.getString("picture"));
			System.out.println(leagalBean.getPicture());
			list.add(leagalBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

//后端模糊查询后总数
public int findselectCountByLb(String lbString,String tilString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from news where category =? and concat(id,title,content,category,author,source,time,picture,homepage,link) like ? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		statement.setString(2,"%" + tilString + "%");
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
			System.out.print("totalCount :"+count);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}

public int findselectCountByAll(String tilString) {
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	int count = 0;
	String sql = "select count(*) totalCount from admin like ? ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1,"%" + tilString + "%");
		rs = statement.executeQuery();
		if (rs.next()) {
			count=rs.getInt("totalCount");
			System.out.print("totalCount :"+count);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return count;
	
}


public List<LeagalBean> newsAll() {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	//String sql = "select password from admin where username = " + username + "'";
	String sql = "select * from news";
	
	try {
		statement = connection.prepareStatement(sql);
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setId(rs.getInt("id"));
			leagalBean.getName(rs.getString("name"));
			leagalBean.setTime(rs.getDate("time"));
			list.add(leagalBean);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}

	

public List<LeagalBean> newsProselect(String lbString,String tilString,Page page) {
	    List<LeagalBean> list =new ArrayList<LeagalBean>();
	    DBUtil dbUtil = new DBUtil();
	    connection = dbUtil.geConnection();
	    //String sql = "select * from news";
	    String sql ="select * from news " ;
	    connection = dbUtil.geConnection();
	    try {
	     statement = connection.prepareStatement(sql);
	     statement.setString(1, lbString);
	     statement.setString(2,"%" + tilString + "%");
	     statement.setInt(3, page.getBeginIndex());
	     statement.setInt(4, page.getEveryPage());
	     rs = statement.executeQuery();
	     while(rs.next()) {
	    	   LeagalBean leagalBean = new LeagalBean();
			   leagalBean.setId(rs.getInt("id"));
			   leagalBean.setTitle(rs.getString("title"));
			   leagalBean.setContent(rs.getString("content"));
			   leagalBean.setCategory(rs.getString("category"));
			   leagalBean.setAuthor(rs.getString("author"));
			   leagalBean.setSource(rs.getString("source"));
			   leagalBean.setTime(rs.getDate("time"));
			   leagalBean.setPicture(rs.getString("picture"));
			   leagalBean.setPuton(rs.getInt("puton"));
			   list.add(leagalBean);
	     }
	     
	    } catch (SQLException e) {
	     e.printStackTrace();
	    }finally {
	     if (rs != null) {
	      try {
	       rs.close();
	      } catch (SQLException e) {
	       e.printStackTrace();
	      }
	     }
	     if (statement != null) {
	      try {
	       statement.close();
	      } catch (SQLException e) {
	       e.printStackTrace();
	      }
	     }
	     if(connection != null) {
	      try {
	       connection.close();
	      } catch (SQLException e) {
	       e.printStackTrace();
	      }
	     }
	     dbUtil.close();
	    }
	    return list;
	    
	   }


//通过时间排序
public List<LeagalBean> findAllByLbForPX(String lbString) {
System.out.println("run findAllByLb " + lbString);
List<LeagalBean> list = new ArrayList<LeagalBean>();
DBUtil dbUtil = new DBUtil();
connection = dbUtil.geConnection();
String sql = "select * from news where category = ? order by time desc";
try {
statement = connection.prepareStatement(sql);
statement.setString(1, lbString);
rs = statement.executeQuery();
while (rs.next()) {
 LeagalBean leagalBean = new LeagalBean();
 leagalBean.setId(rs.getInt("id"));
 leagalBean.setTitle(rs.getString("title"));
 leagalBean.setContent(rs.getString("content"));
 leagalBean.setTime(rs.getDate("time"));
 leagalBean.setPicture(rs.getString("picture"));
 leagalBean.setAuthor(rs.getString("author"));
 leagalBean.setLink(rs.getString("link"));
 leagalBean.setSource(rs.getString("source"));
 leagalBean.setCategory(rs.getString("category"));
 leagalBean.setHomepage(rs.getInt("homepage"));
 list.add(leagalBean);
 System.out.println("findAllByLb  " + leagalBean);
}

} catch (Exception e) {
e.printStackTrace();
}finally {
if (rs != null) {
 try {
  rs.close();
 } catch (SQLException e) {
  e.printStackTrace();
 }
}
if (statement != null) {
 try {
  statement.close();
 } catch (SQLException e) {
  e.printStackTrace();
 }
}
if(connection != null) {
 try {
  connection.close();
 } catch (SQLException e) {
  e.printStackTrace();
 }
}
dbUtil.close();
}
return list;

}

//查找上一条的ID
public LeagalBean findPreNewsById(List<LeagalBean> listLeagalBean, int id) {
LeagalBean leagalBean = null;
for(int i=0;i<listLeagalBean.size();i++) {
if(id == listLeagalBean.get(i).getId()&&i!=0) {
 leagalBean = listLeagalBean.get(i-1);
 return leagalBean;
}
}
return leagalBean;

}


public LeagalBean findNextNewsById(List<LeagalBean> listLeagalBean,int id) {
LeagalBean leagalBean = null;
for(int i=0;i<listLeagalBean.size();i++) {
if(id == listLeagalBean.get(i).getId()&&i+1<listLeagalBean.size()) {
 leagalBean = listLeagalBean.get(i+1);
 return leagalBean;
}
}
return leagalBean;
}



public LeagalBean findPreNewById(int id) {
	System.out.println("run findNewsByid  nad ib");
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	LeagalBean leagalBean = new LeagalBean();
	String sql = "select * from news where id < ? and homepage order by id desc limit 1 ";
	try {
		statement = connection.prepareStatement(sql);
		statement.setInt(1, id);
		System.out.println(sql);
		rs = statement.executeQuery();
		System.out.println(sql);
		
		if(rs.next()) {
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			leagalBean.setTime(rs.getDate("time"));
			leagalBean.setAuthor(rs.getString("author"));
			leagalBean.setSource(rs.getString("source"));
			leagalBean.setPicture(rs.getString("picture"));
			leagalBean.setLink(rs.getString("link"));
			
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}

	return leagalBean;
	
}




public LeagalBean findNextNewById( int id) {
	
	DBUtil dbUtil = new DBUtil();
	connection = dbUtil.geConnection();
	LeagalBean leagalBean = new LeagalBean();
	String sql = "select * from news where id > ? and homepage=1 and puton=1 order by id asc limit 1 ";
	try {
		
		statement = connection.prepareStatement(sql);
		statement.setInt(1, id);
		System.out.println(sql);
		rs = statement.executeQuery();
		System.out.println(sql);
		if(rs.next()) {
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			leagalBean.setTime(rs.getDate("time"));
			leagalBean.setAuthor(rs.getString("author"));
			leagalBean.setSource(rs.getString("source"));
			leagalBean.setPicture(rs.getString("picture"));
			leagalBean.setLink(rs.getString("link"));
			leagalBean.setPuton(rs.getInt("puton"));
			System.out.println(leagalBean.getPicture());
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}

	return leagalBean;
	
}

//按类别查找
public  List<LeagalBean> findOnindexbylb(String lbString) {
	List<LeagalBean> list = new ArrayList<LeagalBean>();
	
	DBUtil dbUtil = new DBUtil();
	String sql = "select * from news where category=? and homepage=1 order by time desc ";
	
	connection = dbUtil.geConnection();
	try {
		statement = connection.prepareStatement(sql);
		statement.setString(1, lbString);
		System.out.println(statement.toString());
		rs = statement.executeQuery();
		while(rs.next()) {
			LeagalBean leagalBean = new LeagalBean();
			leagalBean.setId(rs.getInt("id"));
			leagalBean.setTitle(rs.getString("title"));
			leagalBean.setContent(rs.getString("content"));
			leagalBean.setAuthor(rs.getString("author"));
			leagalBean.setSource(rs.getString("source"));
			leagalBean.setTime(rs.getDate("time"));
			leagalBean.setPicture(rs.getString("picture"));
			list.add(leagalBean);
			System.out.println(rs.getString("title"));
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		dbUtil.close();
	}
	return list;
	
}




	
}
